# # # import numpy as np
# # # import pandas as pd
# # # import matplotlib.pyplot as plt
# # #
# # # plt.rcParams['font.sans-serif'] = ['SimHei']  # 使用黑体
# # # plt.rcParams['axes.unicode_minus'] = False
# # # brand_file_path = '餐饮连锁品牌数据.xlsx'
# # # sheet_names = ['门店信息', '菜品信息', '营销记录', '顾客评价']
# # # sheet_name = sheet_names[3]  # 顾客评价表
# # # df_review = pd.read_excel(brand_file_path, sheet_name)
# # #
# # # print("\n【空值统计】")
# # # print(df_review.isnull().sum())
# # # print("======================")
# # #
# # # # --- 测试1：查看哪些列空值最多 ---
# # # missing_rate = df_review.isnull().mean().sort_values(ascending=False)
# # # print("\n【空值比例（Top 10）】")
# # # print(missing_rate.head(10))
# # # print("======================")
# # #
# # # # --- 处理方式：删除含有空值的行 ---
# # # before_rows = df_review.shape[0]
# # # df_review.dropna(inplace=True)
# # # after_rows = df_review.shape[0]
# # # print(f"\n【空值处理】已删除 {before_rows - after_rows} 行包含空值的数据。")
# # #
# # # # --- 验证：是否还有空值 ---
# # # print("\n【空值处理后验证】")
# # # print(df_review.isnull().sum().sum())
# # # print('======================')
# # # print('======================')
# # #
# # # # ======================================
# # # # 3️⃣ 检查并处理重复值
# # # # ======================================
# # # print("\n【重复值检测】")
# # # print(df_review.duplicated().sum())
# # # print('================================')
# # #
# # # if df_review.duplicated().sum() > 0:
# # #     print(df_review[df_review.duplicated()])
# # #
# # # df_review.drop_duplicates(inplace=True)
# # #
# # # print("\n【重复值处理后验证】")
# # # print(df_review.duplicated().sum())
# # # print('======================')
# # # print('======================')
# # # print('=============================')
# # #
# # # # ======================================
# # # # 4️⃣ 识别并修正异常值
# # # # ======================================
# # #
# # # print("\n【数值列统计描述】")
# # # print(df_review.describe())
# # #
# # # print('评价日期--------------------------------------')
# # # outer = '评价日期'
# # # if outer in df_review.columns:
# # #     # 转换为日期格式
# # #     df_review[outer] = pd.to_datetime(df_review[outer], errors='coerce')
# # #
# # #     # 定义异常值的阈值
# # #     upper = pd.Timestamp('2025-11-01')
# # #     lower = pd.Timestamp('2020-01-01')
# # #
# # #     # 标记异常值
# # #     outliers = df_review[(df_review[outer] > upper) | (df_review[outer] < lower) | (df_review[outer].isna())]
# # #     print(f"\n检测到异常值数量：{len(outliers)}")
# # #
# # #     # 替换异常值为中位数日期
# # #     median_date = df_review[outer].median()
# # #     df_review.loc[
# # #         (df_review[outer] > upper) | (df_review[outer] < lower) | (df_review[outer].isna()), outer] = median_date
# # #     print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数日期 {median_date}。")
# # #
# # # print('总体评分(1-5)--------------------------------------')
# # # max_score = 5
# # # min_score = 1
# # # outer = '总体评分(1-5)'
# # # if outer in df_review.columns:
# # #     # 定义异常值的阈值
# # #     upper = max_score
# # #     lower = min_score
# # #
# # #     # 标记异常值
# # #     outliers = df_review[(df_review[outer] > upper) | (df_review[outer] < lower)]
# # #     print(f"\n检测到异常值数量：{len(outliers)}")
# # #
# # #     # 替换异常值为中位数并转换为正整数
# # #     median_value = int(round(df_review[outer].median()))
# # #     df_review.loc[(df_review[outer] > upper) | (df_review[outer] < lower), outer] = median_value
# # #     # 确保所有评分为正整数
# # #     df_review[outer] = df_review[outer].apply(lambda x: int(round(x)) if pd.notnull(x) else x)
# # #     print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_value}。")
# # #
# # # print('口味评分(1-5)--------------------------------------')
# # # outer = '口味评分(1-5)'
# # # if outer in df_review.columns:
# # #     # 定义异常值的阈值
# # #     upper = max_score
# # #     lower = min_score
# # #
# # #     # 标记异常值
# # #     outliers = df_review[(df_review[outer] > upper) | (df_review[outer] < lower)]
# # #     print(f"\n检测到异常值数量：{len(outliers)}")
# # #
# # #     # 替换异常值为中位数并转换为正整数
# # #     median_value = int(round(df_review[outer].median()))
# # #     df_review.loc[(df_review[outer] > upper) | (df_review[outer] < lower), outer] = median_value
# # #     # 确保所有评分为正整数
# # #     df_review[outer] = df_review[outer].apply(lambda x: int(round(x)) if pd.notnull(x) else x)
# # #     print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_value}。")
# # #
# # # print('分量评分(1-5)--------------------------------------')
# # # outer = '分量评分(1-5)'
# # # if outer in df_review.columns:
# # #     # 定义异常值的阈值
# # #     upper = max_score
# # #     lower = min_score
# # #
# # #     # 标记异常值
# # #     outliers = df_review[(df_review[outer] > upper) | (df_review[outer] < lower)]
# # #     print(f"\n检测到异常值数量：{len(outliers)}")
# # #
# # #     # 替换异常值为中位数并转换为正整数
# # #     median_value = int(round(df_review[outer].median()))
# # #     df_review.loc[(df_review[outer] > upper) | (df_review[outer] < lower), outer] = median_value
# # #     # 确保所有评分为正整数
# # #     df_review[outer] = df_review[outer].apply(lambda x: int(round(x)) if pd.notnull(x) else x)
# # #     print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_value}。")
# # #
# # # print('价格评分(1-5)--------------------------------------')
# # # outer = '价格评分(1-5)'
# # # if outer in df_review.columns:
# # #     # 定义异常值的阈值
# # #     upper = max_score
# # #     lower = min_score
# # #
# # #     # 标记异常值
# # #     outliers = df_review[(df_review[outer] > upper) | (df_review[outer] < lower)]
# # #     print(f"\n检测到异常值数量：{len(outliers)}")
# # #
# # #     # 替换异常值为中位数并转换为正整数
# # #     median_value = int(round(df_review[outer].median()))
# # #     df_review.loc[(df_review[outer] > upper) | (df_review[outer] < lower), outer] = median_value
# # #     # 确保所有评分为正整数
# # #     df_review[outer] = df_review[outer].apply(lambda x: int(round(x)) if pd.notnull(x) else x)
# # #     print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_value}。")
# # #
# # # print('颜值评分(1-5)--------------------------------------')
# # # outer = '颜值评分(1-5)'
# # # if outer in df_review.columns:
# # #     # 定义异常值的阈值
# # #     upper = max_score
# # #     lower = min_score
# # #
# # #     # 标记异常值
# # #     outliers = df_review[(df_review[outer] > upper) | (df_review[outer] < lower)]
# # #     print(f"\n检测到异常值数量：{len(outliers)}")
# # #
# # #     # 替换异常值为中位数并转换为正整数
# # #     median_value = int(round(df_review[outer].median()))
# # #     df_review.loc[(df_review[outer] > upper) | (df_review[outer] < lower), outer] = median_value
# # #     # 确保所有评分为正整数
# # #     df_review[outer] = df_review[outer].apply(lambda x: int(round(x)) if pd.notnull(x) else x)
# # #     print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_value}。")
# # #
# # # print('上菜速度评分(1-5)--------------------------------------')
# # # outer = '上菜速度评分(1-5)'
# # # if outer in df_review.columns:
# # #     # 定义异常值的阈值
# # #     upper = max_score
# # #     lower = min_score
# # #
# # #     # 标记异常值
# # #     outliers = df_review[(df_review[outer] > upper) | (df_review[outer] < lower)]
# # #     print(f"\n检测到异常值数量：{len(outliers)}")
# # #
# # #     # 替换异常值为中位数并转换为正整数
# # #     median_value = int(round(df_review[outer].median()))
# # #     df_review.loc[(df_review[outer] > upper) | (df_review[outer] < lower), outer] = median_value
# # #     # 确保所有评分为正整数
# # #     df_review[outer] = df_review[outer].apply(lambda x: int(round(x)) if pd.notnull(x) else x)
# # #     print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_value}。")
# # #
# # # print('======================================')
# # # print('======================================')
# # #
# # # # ======================================
# # # # 6️⃣ 导出清洗后的数据
# # # # ======================================
# # # clean_path = '餐饮连锁品牌数据_顾客评价4_cleaned.xlsx'
# # # df_review.to_excel(clean_path, index=False)
# # # print(f"\n✅ 数据清洗完成，已保存至 {clean_path}")
# #
# # # 异常值处理，在"售价（元）"的异常值处理要保证其值最多保留一位小数：
# # import numpy as np
# # import pandas as pd
# # import matplotlib.pyplot as plt
# #
# # plt.rcParams['font.sans-serif'] = ['SimHei']  # 使用黑体
# # plt.rcParams['axes.unicode_minus'] = False
# # brand_file_path = '餐饮连锁品牌数据.xlsx'
# # sheet_name = '菜品信息'
# # df_dish = pd.read_excel(brand_file_path, sheet_name)
# #
# # print("\n【空值统计】")
# # print(df_dish.isnull().sum())
# # print("======================")
# #
# # # --- 测试1：查看哪些列空值最多 ---
# # missing_rate = df_dish.isnull().mean().sort_values(ascending=False)
# # print("\n【空值比例（Top 10）】")
# # print(missing_rate.head(10))
# # print("======================")
# #
# # # --- 删除空值 ---
# # before_rows = df_dish.shape[0]
# # df_dish.dropna(inplace=True)
# # after_rows = df_dish.shape[0]
# # print(f"\n【空值处理】已删除 {before_rows - after_rows} 行包含空值的数据。")
# #
# # # --- 验证空值 ---
# # print("\n【空值处理后验证】")
# # print(df_dish.isnull().sum().sum())
# # print('======================')
# # print('======================')
# #
# # # ======================================
# # # 3️⃣ 检查并处理重复值
# # # ======================================
# # print("\n【重复值检测】")
# # print(df_dish.duplicated().sum())
# # print('================================')
# #
# # if df_dish.duplicated().sum() > 0:
# #     print(df_dish[df_dish.duplicated()])
# #
# # df_dish.drop_duplicates(inplace=True)
# #
# # print("\n【重复值处理后验证】")
# # print(df_dish.duplicated().sum())
# # print('======================')
# # print('======================')
# #
# # # ======================================
# # # 4️⃣ 识别并修正异常值
# # # ======================================
# # print("\n【数值列统计描述】")
# # print(df_dish.describe())
# #
# # # -------------------------
# # # 菜品ID
# # # -------------------------
# # outer = '菜品ID'
# # if outer in df_dish.columns:
# #     # 去除非字符串类型ID
# #     invalid_ids = df_dish[~df_dish[outer].astype(str).str.startswith('DSH')]
# #     print(f"\n检测到异常菜品ID数量：{len(invalid_ids)}")
# #     df_dish.loc[~df_dish[outer].astype(str).str.startswith('DSH'), outer] = np.nan
# #
# # # -------------------------
# # # 售价(元) - 优化后的异常值处理
# # # -------------------------
# # outer = '售价(元)'
# # if outer in df_dish.columns:
# #     mean_sales = df_dish[outer].mean()
# #     std_sales = df_dish[outer].std()
# #     upper = mean_sales + 2.5 * std_sales  # 调整为2.5倍标准差，更宽容
# #     lower = max(mean_sales - 2.5 * std_sales, 0)
# #
# #     outliers = df_dish[(df_dish[outer] > upper) | (df_dish[outer] < lower)]
# #     print(f"\n检测到异常售价数量：{len(outliers)}")
# #     median_sales = df_dish[outer].median()
# #
# #     # 先替换异常值为中位数，然后统一保留一位小数
# #     df_dish.loc[(df_dish[outer] > upper) | (df_dish[outer] < lower), outer] = median_sales
# #     # 确保所有售价数据最多保留一位小数
# #     df_dish[outer] = df_dish[outer].round(2)
# #
# #     print(f"【异常值处理】已将 {len(outliers)} 个售价异常值替换为中位数 {median_sales:.1f}，并确保所有值保留一位小数。")
# #
# # # -------------------------
# # # 成本(元)
# # # -------------------------
# # outer = '成本(元)'
# # if outer in df_dish.columns:
# #     mean_cost = df_dish[outer].mean()
# #     std_cost = df_dish[outer].std()
# #     upper = mean_cost + 2.8 * std_cost  # 成本波动略大，放宽到2.8倍标准差
# #     lower = max(mean_cost - 2.8 * std_cost, 0)
# #     outliers = df_dish[(df_dish[outer] > upper) | (df_dish[outer] < lower)]
# #     print(f"\n检测到异常成本数量：{len(outliers)}")
# #     median_cost = df_dish[outer].median()
# #     df_dish.loc[(df_dish[outer] > upper) | (df_dish[outer] < lower), outer] = median_cost
# #     print(f"【异常值处理】已将 {len(outliers)} 个成本异常值替换为中位数 {median_cost}。")
# #
# # # -------------------------
# # # 制作时间(分钟)
# # # -------------------------
# # outer = '制作时间(分钟)'
# # if outer in df_dish.columns:
# #     upper = 120  # 认为超过2小时属于异常
# #     lower = 0  # 不存在负值
# #     outliers = df_dish[(df_dish[outer] > upper) | (df_dish[outer] < lower)]
# #     print(f"\n检测到异常制作时间数量：{len(outliers)}")
# #     median_time = df_dish[outer].median()
# #     df_dish.loc[(df_dish[outer] > upper) | (df_dish[outer] < lower), outer] = median_time
# #     print(f"【异常值处理】已将 {len(outliers)} 个制作时间异常值替换为中位数 {median_time}。")
# #
# # # -------------------------
# # # 是否核心菜品
# # # -------------------------
# # outer = '是否核心菜品'
# # if outer in df_dish.columns:
# #     invalid_core = df_dish[~df_dish[outer].isin(['是', '否'])]
# #     print(f"\n检测到异常核心菜品标记数量：{len(invalid_core)}")
# #     mode_core = df_dish[outer].mode()[0]
# #     df_dish.loc[~df_dish[outer].isin(['是', '否']), outer] = mode_core
# #     print(f"【异常值处理】已将 {len(invalid_core)} 个异常标记替换为众数 {mode_core}。")
# #
# # print('======================================')
# # print('======================================')
# #
# # # ======================================
# # # 6️⃣ 导出清洗后的数据
# # # ======================================
# # clean_path = '餐饮连锁品牌数据_菜品信息4_cleaned.xlsx'
# # df_dish.to_excel(clean_path, index=False)
# # print(f"\n✅ 数据清洗完成，已保存至 {clean_path}")
#
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

plt.rcParams['font.sans-serif'] = ['SimHei']  # 使用黑体
plt.rcParams['axes.unicode_minus'] = False
brand_file_path = '餐饮连锁品牌数据.xlsx'
sheet_names=['门店信息','菜品信息','销售记录','顾客评价']
sheet_name=sheet_names[2]  # 销售记录表
df_sales = pd.read_excel(brand_file_path, sheet_name)

print("\n【空值统计】")
print(df_sales.isnull().sum())
print("======================")

# --- 测试1：查看哪些列空值最多 ---
missing_rate = df_sales.isnull().mean().sort_values(ascending=False)
print("\n【空值比例（Top 10）】")
print(missing_rate.head(10))
print("======================")

# --- 处理方式：删除含有空值的行 ---
before_rows = df_sales.shape[0]
df_sales.dropna(inplace=True)
after_rows = df_sales.shape[0]
print(f"\n【空值处理】已删除 {before_rows - after_rows} 行包含空值的数据。")

# --- 验证：是否还有空值 ---
print("\n【空值处理后验证】")
print(df_sales.isnull().sum().sum())
print('======================')
print('======================')

# ======================================
# 3️⃣ 检查并处理重复值
# ======================================
print("\n【重复值检测】")
print(df_sales.duplicated().sum())
print('================================')

if df_sales.duplicated().sum() > 0:
    print(df_sales[df_sales.duplicated()])

df_sales.drop_duplicates(inplace=True)

print("\n【重复值处理后验证】")
print(df_sales.duplicated().sum())
print('======================')
print('======================')
print('=============================')

# ======================================
# 4️⃣ 识别并修正异常值
# ======================================

print("\n【数值列统计描述】")
print(df_sales.describe())

print('星期--------------------------------------')
outer='星期'
if outer in df_sales.columns:
    # 定义异常值的阈值
    upper = 7
    lower = 1

    # 标记异常值
    outliers = df_sales[(df_sales[outer] > upper) | (df_sales[outer] < lower)]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为中位数并取整
    median_value = int(round(df_sales[outer].median()))
    df_sales.loc[(df_sales[outer] > upper) | (df_sales[outer] < lower), outer] = median_value
    # 确保所有值为正整数
    df_sales[outer] = df_sales[outer].apply(lambda x: int(round(x)) if pd.notnull(x) else x)
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_value}。")

print('销售时间(时)--------------------------------------')
outer='销售时间(时)'
if outer in df_sales.columns:
    # 定义异常值的阈值
    upper = 23
    lower = 0

    # 标记异常值
    outliers = df_sales[(df_sales[outer] > upper) | (df_sales[outer] < lower) | (df_sales[outer] < 0)]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为中位数并取整
    median_value = int(round(df_sales[outer].median()))
    df_sales.loc[(df_sales[outer] > upper) | (df_sales[outer] < lower) | (df_sales[outer] < 0), outer] = median_value
    # 确保所有值为正整数
    df_sales[outer] = df_sales[outer].apply(lambda x: int(round(x)) if pd.notnull(x) else x)
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_value}。")

print('时段--------------------------------------')
outer='时段'
if outer in df_sales.columns:
    # 定义正常时段
    valid_periods = ['早餐', '午餐', '晚餐', '夜宵']

    # 标记异常值
    outliers = df_sales[~df_sales[outer].isin(valid_periods)]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为众数
    mode_value = df_sales[outer].mode()[0]
    df_sales.loc[~df_sales[outer].isin(valid_periods), outer] = mode_value
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为众数 {mode_value}。")

print('销售数量--------------------------------------')
outer='销售数量'
if outer in df_sales.columns:
    # 定义异常值的阈值 - 确保为正整数
    upper = df_sales[outer].mean() + 3 * df_sales[outer].std()
    lower = 1  # 最小销售数量为1

    # 标记异常值
    outliers = df_sales[(df_sales[outer] > upper) | (df_sales[outer] < lower) | (df_sales[outer] < 0)]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为中位数并取整
    median_value = int(round(df_sales[outer].median()))
    df_sales.loc[(df_sales[outer] > upper) | (df_sales[outer] < lower) | (df_sales[outer] < 0), outer] = median_value
    # 确保所有值为正整数
    df_sales[outer] = df_sales[outer].apply(lambda x: int(round(x)) if pd.notnull(x) else x)
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_value}。")

print('销售单价(元)--------------------------------------')
outer='销售单价(元)'
if outer in df_sales.columns:
    mean_value = df_sales[outer].mean()
    std_value = df_sales[outer].std()
    upper = mean_value + 3 * std_value
    lower = max(mean_value - 3 * std_value, 0)

    # 标记异常值
    outliers = df_sales[(df_sales[outer] > upper) | (df_sales[outer] < lower) | (df_sales[outer] < 0)]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为中位数并保留两位小数
    median_value = round(df_sales[outer].median(), 2)
    df_sales.loc[(df_sales[outer] > upper) | (df_sales[outer] < lower) | (df_sales[outer] < 0), outer] = median_value
    # 确保所有值保留两位小数
    df_sales[outer] = df_sales[outer].round(2)
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_value}。")

print('是否外卖--------------------------------------')
outer='是否外卖'
if outer in df_sales.columns:
    # 定义正常值
    valid_values = ['是', '否']

    # 标记异常值
    outliers = df_sales[~df_sales[outer].isin(valid_values)]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为众数
    mode_value = df_sales[outer].mode()[0]
    df_sales.loc[~df_sales[outer].isin(valid_values), outer] = mode_value
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为众数 {mode_value}。")

print('======================================')
print('======================================')

# ======================================
# 6️⃣ 导出清洗后的数据
# ======================================
clean_path = '餐饮连锁品牌数据_销售记录3_cleaned.xlsx'
df_sales.to_excel(clean_path, index=False)
print(f"\n✅ 数据清洗完成，已保存至 {clean_path}")

